3 * Microsoft SQL Server-specific installer.
5 * This program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License along
16 * with this program; if not, write to the Free Software Foundation, Inc.,
17 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 * http://www.gnu.org/copyleft/gpl.html
24 use Wikimedia\Rdbms\Database
;
25 use Wikimedia\Rdbms\DBQueryError
;
26 use Wikimedia\Rdbms\DBConnectionError
;
29 * Class for setting up the MediaWiki database using Microsoft SQL Server.
34 class MssqlInstaller
extends DatabaseInstaller
{
36 protected $globalNames = [
43 'wgDBWindowsAuthentication',
46 protected $internalDefaults = [
47 '_InstallUser' => 'sa',
48 '_InstallWindowsAuthentication' => 'sqlauth',
49 '_WebWindowsAuthentication' => 'sqlauth',
52 // SQL Server 2005 RTM
53 // @todo Are SQL Express version numbers different?)
54 public static $minimumVersion = '9.00.1399';
55 protected static $notMinimumVersionMessage = 'config-mssql-old';
57 // These are schema-level privs
58 // Note: the web user will be created will full permissions if possible, this permission
59 // list is only used if we are unable to grant full permissions.
60 public $webUserPrivs = [
71 public function getName() {
78 public function isCompiled() {
79 return self
::checkExtension( 'sqlsrv' );
85 public function getConnectForm() {
86 if ( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth' ) {
87 $displayStyle = 'display: none;';
89 $displayStyle = 'display: block;';
92 return $this->getTextBox(
96 $this->parent
->getHelpBox( 'config-db-host-help' )
98 Html
::openElement( 'fieldset' ) .
99 Html
::element( 'legend', [], wfMessage( 'config-db-wiki-settings' )->text() ) .
100 $this->getTextBox( 'wgDBname', 'config-db-name', [ 'dir' => 'ltr' ],
101 $this->parent
->getHelpBox( 'config-db-name-help' ) ) .
102 $this->getTextBox( 'wgDBmwschema', 'config-db-schema', [ 'dir' => 'ltr' ],
103 $this->parent
->getHelpBox( 'config-db-schema-help' ) ) .
104 $this->getTextBox( 'wgDBprefix', 'config-db-prefix', [ 'dir' => 'ltr' ],
105 $this->parent
->getHelpBox( 'config-db-prefix-help' ) ) .
106 Html
::closeElement( 'fieldset' ) .
107 Html
::openElement( 'fieldset' ) .
108 Html
::element( 'legend', [], wfMessage( 'config-db-install-account' )->text() ) .
109 $this->getRadioSet( [
110 'var' => '_InstallWindowsAuthentication',
111 'label' => 'config-mssql-auth',
112 'itemLabelPrefix' => 'config-mssql-',
113 'values' => [ 'sqlauth', 'windowsauth' ],
116 'class' => 'showHideRadio',
117 'rel' => 'dbCredentialBox',
120 'class' => 'hideShowRadio',
121 'rel' => 'dbCredentialBox',
124 'help' => $this->parent
->getHelpBox( 'config-mssql-install-auth' )
126 Html
::openElement( 'div', [ 'id' => 'dbCredentialBox', 'style' => $displayStyle ] ) .
129 'config-db-username',
131 $this->parent
->getHelpBox( 'config-db-install-username' )
133 $this->getPasswordBox(
135 'config-db-password',
137 $this->parent
->getHelpBox( 'config-db-install-password' )
139 Html
::closeElement( 'div' ) .
140 Html
::closeElement( 'fieldset' );
143 public function submitConnectForm() {
144 // Get variables from the request.
145 $newValues = $this->setVarsFromRequest( [
153 $status = Status
::newGood();
154 if ( !strlen( $newValues['wgDBserver'] ) ) {
155 $status->fatal( 'config-missing-db-host' );
157 if ( !strlen( $newValues['wgDBname'] ) ) {
158 $status->fatal( 'config-missing-db-name' );
159 } elseif ( !preg_match( '/^[a-z0-9_]+$/i', $newValues['wgDBname'] ) ) {
160 $status->fatal( 'config-invalid-db-name', $newValues['wgDBname'] );
162 if ( !preg_match( '/^[a-z0-9_]*$/i', $newValues['wgDBmwschema'] ) ) {
163 $status->fatal( 'config-invalid-schema', $newValues['wgDBmwschema'] );
165 if ( !preg_match( '/^[a-z0-9_]*$/i', $newValues['wgDBprefix'] ) ) {
166 $status->fatal( 'config-invalid-db-prefix', $newValues['wgDBprefix'] );
168 if ( !$status->isOK() ) {
172 // Check for blank schema and remap to dbo
173 if ( $newValues['wgDBmwschema'] === '' ) {
174 $this->setVar( 'wgDBmwschema', 'dbo' );
178 $this->setVarsFromRequest( [
181 '_InstallWindowsAuthentication'
185 $status = $this->getConnection();
186 if ( !$status->isOK() ) {
190 * @var Database $conn
192 $conn = $status->value
;
195 return static::meetsMinimumRequirement( $conn->getServerVersion() );
201 public function openConnection() {
202 global $wgDBWindowsAuthentication;
203 $status = Status
::newGood();
204 $user = $this->getVar( '_InstallUser' );
205 $password = $this->getVar( '_InstallPassword' );
207 if ( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth' ) {
208 // Use Windows authentication for this connection
209 $wgDBWindowsAuthentication = true;
211 $wgDBWindowsAuthentication = false;
215 $db = Database
::factory( 'mssql', [
216 'host' => $this->getVar( 'wgDBserver' ),
217 'port' => $this->getVar( 'wgDBport' ),
219 'password' => $password,
222 'schema' => $this->getVar( 'wgDBmwschema' ),
223 'tablePrefix' => $this->getVar( 'wgDBprefix' ) ] );
224 $db->prepareStatements( false );
225 $db->scrollableCursor( false );
226 $status->value
= $db;
227 } catch ( DBConnectionError
$e ) {
228 $status->fatal( 'config-connection-error', $e->getMessage() );
234 public function preUpgrade() {
235 global $wgDBuser, $wgDBpassword;
237 $status = $this->getConnection();
238 if ( !$status->isOK() ) {
239 $this->parent
->showStatusError( $status );
244 * @var Database $conn
246 $conn = $status->value
;
247 $conn->selectDB( $this->getVar( 'wgDBname' ) );
249 # Normal user and password are selected after this step, so for now
250 # just copy these two
251 $wgDBuser = $this->getVar( '_InstallUser' );
252 $wgDBpassword = $this->getVar( '_InstallPassword' );
256 * Return true if the install user can create accounts
260 public function canCreateAccounts() {
261 $status = $this->getConnection();
262 if ( !$status->isOK() ) {
265 /** @var Database $conn */
266 $conn = $status->value
;
268 // We need the server-level ALTER ANY LOGIN permission to create new accounts
269 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( NULL, 'SERVER' )" );
271 'ALTER ANY LOGIN' => false,
272 'CONTROL SERVER' => false,
275 foreach ( $res as $row ) {
276 $serverPrivs[$row->permission_name
] = true;
279 if ( !$serverPrivs['ALTER ANY LOGIN'] ) {
283 // Check to ensure we can grant everything needed as well
284 // We can't actually tell if we have WITH GRANT OPTION for a given permission, so we assume we do
285 // and just check for the permission
286 // https://technet.microsoft.com/en-us/library/ms178569.aspx
287 // The following array sets up which permissions imply whatever permissions we specify
289 // schema database server
290 'DELETE' => [ 'DELETE', 'CONTROL SERVER' ],
291 'EXECUTE' => [ 'EXECUTE', 'CONTROL SERVER' ],
292 'INSERT' => [ 'INSERT', 'CONTROL SERVER' ],
293 'SELECT' => [ 'SELECT', 'CONTROL SERVER' ],
294 'UPDATE' => [ 'UPDATE', 'CONTROL SERVER' ],
297 $grantOptions = array_flip( $this->webUserPrivs
);
299 // Check for schema and db-level permissions, but only if the schema/db exists
300 $schemaPrivs = $dbPrivs = [
308 $dbPrivs['ALTER ANY USER'] = false;
310 if ( $this->databaseExists( $this->getVar( 'wgDBname' ) ) ) {
311 $conn->selectDB( $this->getVar( 'wgDBname' ) );
312 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( NULL, 'DATABASE' )" );
314 foreach ( $res as $row ) {
315 $dbPrivs[$row->permission_name
] = true;
318 // If the db exists, we need ALTER ANY USER privs on it to make a new user
319 if ( !$dbPrivs['ALTER ANY USER'] ) {
323 if ( $this->schemaExists( $this->getVar( 'wgDBmwschema' ) ) ) {
324 // wgDBmwschema is validated to only contain alphanumeric + underscore, so this is safe
325 $res = $conn->query( "SELECT permission_name FROM sys.fn_my_permissions( "
326 . "'{$this->getVar( 'wgDBmwschema' )}', 'SCHEMA' )" );
328 foreach ( $res as $row ) {
329 $schemaPrivs[$row->permission_name
] = true;
334 // Now check all the grants we'll need to be doing to see if we can
335 foreach ( $this->webUserPrivs
as $permission ) {
336 if ( ( isset( $schemaPrivs[$permission] ) && $schemaPrivs[$permission] )
337 ||
( isset( $dbPrivs[$implied[$permission][0]] )
338 && $dbPrivs[$implied[$permission][0]] )
339 ||
( isset( $serverPrivs[$implied[$permission][1]] )
340 && $serverPrivs[$implied[$permission][1]] )
342 unset( $grantOptions[$permission] );
346 if ( count( $grantOptions ) ) {
347 // Can't grant everything
357 public function getSettingsForm() {
358 if ( $this->canCreateAccounts() ) {
359 $noCreateMsg = false;
361 $noCreateMsg = 'config-db-web-no-create-privs';
364 $wrapperStyle = $this->getVar( '_SameAccount' ) ?
'display: none' : '';
365 $displayStyle = $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth'
368 $s = Html
::openElement( 'fieldset' ) .
369 Html
::element( 'legend', [], wfMessage( 'config-db-web-account' )->text() ) .
371 '_SameAccount', 'config-db-web-account-same',
372 [ 'class' => 'hideShowRadio', 'rel' => 'dbOtherAccount' ]
374 Html
::openElement( 'div', [ 'id' => 'dbOtherAccount', 'style' => $wrapperStyle ] ) .
375 $this->getRadioSet( [
376 'var' => '_WebWindowsAuthentication',
377 'label' => 'config-mssql-auth',
378 'itemLabelPrefix' => 'config-mssql-',
379 'values' => [ 'sqlauth', 'windowsauth' ],
382 'class' => 'showHideRadio',
383 'rel' => 'dbCredentialBox',
386 'class' => 'hideShowRadio',
387 'rel' => 'dbCredentialBox',
390 'help' => $this->parent
->getHelpBox( 'config-mssql-web-auth' )
392 Html
::openElement( 'div', [ 'id' => 'dbCredentialBox', 'style' => $displayStyle ] ) .
393 $this->getTextBox( 'wgDBuser', 'config-db-username' ) .
394 $this->getPasswordBox( 'wgDBpassword', 'config-db-password' ) .
395 Html
::closeElement( 'div' );
397 if ( $noCreateMsg ) {
398 $s .= $this->parent
->getWarningBox( wfMessage( $noCreateMsg )->plain() );
400 $s .= $this->getCheckBox( '_CreateDBAccount', 'config-db-web-create' );
403 $s .= Html
::closeElement( 'div' ) . Html
::closeElement( 'fieldset' );
411 public function submitSettingsForm() {
412 $this->setVarsFromRequest( [
417 '_WebWindowsAuthentication'
420 if ( $this->getVar( '_SameAccount' ) ) {
421 $this->setVar( '_WebWindowsAuthentication', $this->getVar( '_InstallWindowsAuthentication' ) );
422 $this->setVar( 'wgDBuser', $this->getVar( '_InstallUser' ) );
423 $this->setVar( 'wgDBpassword', $this->getVar( '_InstallPassword' ) );
426 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
427 $this->setVar( 'wgDBuser', '' );
428 $this->setVar( 'wgDBpassword', '' );
429 $this->setVar( 'wgDBWindowsAuthentication', true );
431 $this->setVar( 'wgDBWindowsAuthentication', false );
434 if ( $this->getVar( '_CreateDBAccount' )
435 && $this->getVar( '_WebWindowsAuthentication' ) == 'sqlauth'
436 && strval( $this->getVar( 'wgDBpassword' ) ) == ''
438 return Status
::newFatal( 'config-db-password-empty', $this->getVar( 'wgDBuser' ) );
441 // Validate the create checkbox
442 $canCreate = $this->canCreateAccounts();
444 $this->setVar( '_CreateDBAccount', false );
447 $create = $this->getVar( '_CreateDBAccount' );
451 // Test the web account
452 $user = $this->getVar( 'wgDBuser' );
453 $password = $this->getVar( 'wgDBpassword' );
455 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
456 $user = 'windowsauth';
457 $password = 'windowsauth';
461 Database
::factory( 'mssql', [
462 'host' => $this->getVar( 'wgDBserver' ),
464 'password' => $password,
467 'tablePrefix' => $this->getVar( 'wgDBprefix' ),
468 'schema' => $this->getVar( 'wgDBmwschema' ),
470 } catch ( DBConnectionError
$e ) {
471 return Status
::newFatal( 'config-connection-error', $e->getMessage() );
475 return Status
::newGood();
478 public function preInstall() {
479 # Add our user callback to installSteps, right before the tables are created.
482 'callback' => [ $this, 'setupUser' ],
484 $this->parent
->addInstallStep( $callback, 'tables' );
490 public function setupDatabase() {
491 $status = $this->getConnection();
492 if ( !$status->isOK() ) {
495 /** @var Database $conn */
496 $conn = $status->value
;
497 $dbName = $this->getVar( 'wgDBname' );
498 $schemaName = $this->getVar( 'wgDBmwschema' );
499 if ( !$this->databaseExists( $dbName ) ) {
501 "CREATE DATABASE " . $conn->addIdentifierQuotes( $dbName ),
505 $conn->selectDB( $dbName );
506 if ( !$this->schemaExists( $schemaName ) ) {
508 "CREATE SCHEMA " . $conn->addIdentifierQuotes( $schemaName ),
512 if ( !$this->catalogExists( $schemaName ) ) {
514 "CREATE FULLTEXT CATALOG " . $conn->addIdentifierQuotes( $schemaName ),
518 $this->setupSchemaVars();
526 public function setupUser() {
527 $dbUser = $this->getVar( 'wgDBuser' );
528 if ( $dbUser == $this->getVar( '_InstallUser' )
529 ||
( $this->getVar( '_InstallWindowsAuthentication' ) == 'windowsauth'
530 && $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) ) {
531 return Status
::newGood();
533 $status = $this->getConnection();
534 if ( !$status->isOK() ) {
538 $this->setupSchemaVars();
539 $dbName = $this->getVar( 'wgDBname' );
540 $this->db
->selectDB( $dbName );
541 $password = $this->getVar( 'wgDBpassword' );
542 $schemaName = $this->getVar( 'wgDBmwschema' );
544 if ( $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth' ) {
545 $dbUser = 'windowsauth';
546 $password = 'windowsauth';
549 if ( $this->getVar( '_CreateDBAccount' ) ) {
552 $tryToCreate = false;
555 $escUser = $this->db
->addIdentifierQuotes( $dbUser );
556 $escDb = $this->db
->addIdentifierQuotes( $dbName );
557 $escSchema = $this->db
->addIdentifierQuotes( $schemaName );
558 $grantableNames = [];
559 if ( $tryToCreate ) {
560 $escPass = $this->db
->addQuotes( $password );
562 if ( !$this->loginExists( $dbUser ) ) {
565 $this->db
->selectDB( 'master' );
566 $logintype = $this->getVar( '_WebWindowsAuthentication' ) == 'windowsauth'
568 : "WITH PASSWORD = $escPass";
569 $this->db
->query( "CREATE LOGIN $escUser $logintype" );
570 $this->db
->selectDB( $dbName );
571 $this->db
->query( "CREATE USER $escUser FOR LOGIN $escUser WITH DEFAULT_SCHEMA = $escSchema" );
573 $grantableNames[] = $dbUser;
574 } catch ( DBQueryError
$dqe ) {
575 $this->db
->rollback();
576 $status->warning( 'config-install-user-create-failed', $dbUser, $dqe->getMessage() );
578 } elseif ( !$this->userExists( $dbUser ) ) {
581 $this->db
->selectDB( $dbName );
582 $this->db
->query( "CREATE USER $escUser FOR LOGIN $escUser WITH DEFAULT_SCHEMA = $escSchema" );
584 $grantableNames[] = $dbUser;
585 } catch ( DBQueryError
$dqe ) {
586 $this->db
->rollback();
587 $status->warning( 'config-install-user-create-failed', $dbUser, $dqe->getMessage() );
590 $status->warning( 'config-install-user-alreadyexists', $dbUser );
591 $grantableNames[] = $dbUser;
595 // Try to grant to all the users we know exist or we were able to create
596 $this->db
->selectDB( $dbName );
597 foreach ( $grantableNames as $name ) {
599 // First try to grant full permissions
601 'BACKUP DATABASE', 'BACKUP LOG', 'CREATE FUNCTION', 'CREATE PROCEDURE',
602 'CREATE TABLE', 'CREATE VIEW', 'CREATE FULLTEXT CATALOG', 'SHOWPLAN'
604 $fullPrivList = implode( ', ', $fullPrivArr );
606 $this->db
->query( "GRANT $fullPrivList ON DATABASE :: $escDb TO $escUser", __METHOD__
);
607 $this->db
->query( "GRANT CONTROL ON SCHEMA :: $escSchema TO $escUser", __METHOD__
);
609 } catch ( DBQueryError
$dqe ) {
610 // If that fails, try to grant the limited subset specified in $this->webUserPrivs
612 $privList = implode( ', ', $this->webUserPrivs
);
613 $this->db
->rollback();
615 $this->db
->query( "GRANT $privList ON SCHEMA :: $escSchema TO $escUser", __METHOD__
);
617 } catch ( DBQueryError
$dqe ) {
618 $this->db
->rollback();
619 $status->fatal( 'config-install-user-grant-failed', $dbUser, $dqe->getMessage() );
621 // Also try to grant SHOWPLAN on the db, but don't fail if we can't
622 // (just makes a couple things in mediawiki run slower since
623 // we have to run SELECT COUNT(*) instead of getting the query plan)
625 $this->db
->query( "GRANT SHOWPLAN ON DATABASE :: $escDb TO $escUser", __METHOD__
);
626 } catch ( DBQueryError
$dqe ) {
634 public function createTables() {
635 $status = parent
::createTables();
637 // Do last-minute stuff like fulltext indexes (since they can't be inside a transaction)
638 if ( $status->isOK() ) {
639 $searchindex = $this->db
->tableName( 'searchindex' );
640 $schema = $this->db
->addIdentifierQuotes( $this->getVar( 'wgDBmwschema' ) );
642 $this->db
->query( "CREATE FULLTEXT INDEX ON $searchindex (si_title, si_text) "
643 . "KEY INDEX si_page ON $schema" );
644 } catch ( DBQueryError
$dqe ) {
645 $status->fatal( 'config-install-tables-failed', $dqe->getMessage() );
652 public function getGlobalDefaults() {
653 // The default $wgDBmwschema is null, which breaks Postgres and other DBMSes that require
654 // the use of a schema, so we need to set it here
655 return array_merge( parent
::getGlobalDefaults(), [
656 'wgDBmwschema' => 'mediawiki',
661 * Try to see if the login exists
662 * @param string $user Username to check
665 private function loginExists( $user ) {
666 $res = $this->db
->selectField( 'sys.sql_logins', 1, [ 'name' => $user ] );
671 * Try to see if the user account exists
672 * We assume we already have the appropriate database selected
673 * @param string $user Username to check
676 private function userExists( $user ) {
677 $res = $this->db
->selectField( 'sys.sysusers', 1, [ 'name' => $user ] );
682 * Try to see if a given database exists
683 * @param string $dbName Database name to check
686 private function databaseExists( $dbName ) {
687 $res = $this->db
->selectField( 'sys.databases', 1, [ 'name' => $dbName ] );
692 * Try to see if a given schema exists
693 * We assume we already have the appropriate database selected
694 * @param string $schemaName Schema name to check
697 private function schemaExists( $schemaName ) {
698 $res = $this->db
->selectField( 'sys.schemas', 1, [ 'name' => $schemaName ] );
703 * Try to see if a given fulltext catalog exists
704 * We assume we already have the appropriate database selected
705 * @param string $catalogName Catalog name to check
708 private function catalogExists( $catalogName ) {
709 $res = $this->db
->selectField( 'sys.fulltext_catalogs', 1, [ 'name' => $catalogName ] );
714 * Get variables to substitute into tables.sql and the SQL patch files.
718 public function getSchemaVars() {
720 'wgDBname' => $this->getVar( 'wgDBname' ),
721 'wgDBmwschema' => $this->getVar( 'wgDBmwschema' ),
722 'wgDBuser' => $this->getVar( 'wgDBuser' ),
723 'wgDBpassword' => $this->getVar( 'wgDBpassword' ),
727 public function getLocalSettings() {
728 $schema = LocalSettingsGenerator
::escapePhpString( $this->getVar( 'wgDBmwschema' ) );
729 $prefix = LocalSettingsGenerator
::escapePhpString( $this->getVar( 'wgDBprefix' ) );
730 $windowsauth = $this->getVar( 'wgDBWindowsAuthentication' ) ?
'true' : 'false';
732 return "# MSSQL specific settings
733 \$wgDBWindowsAuthentication = {$windowsauth};
734 \$wgDBmwschema = \"{$schema}\";
735 \$wgDBprefix = \"{$prefix}\";";